{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Задание 1\n",
    "\n",
    "Скачайте с сайта https://grouplens.org/datasets/movielens/ датасет любого размера. Определите какому фильму было выставлено больше всего оценок 5.0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "rating    153\n",
       "Name: Shawshank Redemption, The (1994), dtype: int64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ratings = pd.read_csv('ml-latest/ratings.csv')\n",
    "movies_ratings = ratings.merge(pd.read_csv('ml-latest/movies.csv'), on='movieId', how='left')\n",
    "r = movies_ratings[movies_ratings['rating'] == 5.0].filter(items=['title', 'rating']).groupby('title').count()\n",
    "display(r.sort_values(by='rating', ascending=False).iloc[0])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Задание 2\n",
    "\n",
    "По данным файла power.csv посчитайте суммарное потребление стран Прибалтики (Латвия, Литва и Эстония) категорий 4, 12 и 21 за период с 2005 по 2010 года. Не учитывайте в расчетах отрицательные значения quantity."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "power = pd.read_csv('power.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>quantity</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>country</th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>Estonia</th>\n",
       "      <td>63440.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Latvia</th>\n",
       "      <td>54844.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Lithuania</th>\n",
       "      <td>122296.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           quantity\n",
       "country            \n",
       "Estonia     63440.0\n",
       "Latvia      54844.0\n",
       "Lithuania  122296.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "вaltic_countries_c4_c12_c21 = power[((power['country'] == 'Lithuania') | \n",
    "                                     (power['country'] == 'Latvia') | \n",
    "                                     (power['country'] == 'Estonia')) \n",
    "                                    &\n",
    "                                    ((power['category'] == 4) |\n",
    "                                     (power['category'] == 12) |\n",
    "                                     (power['category'] == 21))\n",
    "                                    &\n",
    "                                    (power['quantity'] >= 0)\n",
    "                                    &\n",
    "                                    (power['year'] >= 2005) \n",
    "                                    &\n",
    "                                    (power['year'] <= 2010)\n",
    "                                   ]\n",
    "вaltic_countries_quantity_sum = вaltic_countries_c4_c12_c21[['country', 'quantity']].groupby('country').sum()\n",
    "display(вaltic_countries_quantity_sum)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Задание 3\n",
    "\n",
    "Выберите страницу любого сайта с табличными данными. Импортируйте таблицы в pandas dataframe.\n",
    "Примеры страниц (необязательно брать именно эти):\n",
    "https://fortrader.org/quotes\n",
    "https://www.finanz.ru/valyuty/v-realnom-vremeni"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "def currency_code_filter(curr_code:str) -> str:\n",
    "    \"\"\"\n",
    "    Функция для очистки кода валюты от лишних символов\n",
    "    \n",
    "    Например, \"JPY/EUR(100:1)\" будет преобразовано в \"JPY/EUR\"\n",
    "    \n",
    "    Входные параметры:\n",
    "    val_code -- код валюты для очистки, тип str\n",
    "    \n",
    "    Выходные значения: отфильтрованный код валюты, тип str\n",
    "    \"\"\"\n",
    "    pattern = r'(\\w{3}/\\w{3})'\n",
    "    result = re.compile(pattern).findall(curr_code)\n",
    "    assert len(result) == 1, f'Функция не смогла корректно распознать код валюты {curr_code}'\n",
    "    \n",
    "    return result[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "html = pd.read_html('https://www.finanz.ru/valyuty/v-realnom-vremeni', \n",
    "                    encoding='utf-8',\n",
    "                    attrs={'class': 'quote_list'})[0]\n",
    "# Удаляем столбец, в котором были графические ссылки\n",
    "del(html['Unnamed: 0'])\n",
    "# Переименовываем столбцы\n",
    "html.columns = ['Валюта','Предложение','Спрос','Предыдущее закрытие','%','Абсолютное значение','Время']\n",
    "# Удаляем некорректные строки, которые содержат названия вложенных таблиц\n",
    "html = html[html['Время'] != 'Время']\n",
    "# Изменяем коды валют, удаляем все лишние символы\n",
    "html['Валюта'] = html['Валюта'].transform(currency_code_filter)\n",
    "# Индексируем все Series по валюте\n",
    "html.index = html['Валюта']\n",
    "# Удаляем стобец с валютами, теперь он у нас в индексе\n",
    "del(html['Валюта'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Предложение</th>\n",
       "      <th>Спрос</th>\n",
       "      <th>Предыдущее закрытие</th>\n",
       "      <th>%</th>\n",
       "      <th>Абсолютное значение</th>\n",
       "      <th>Время</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Валюта</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>GBP/RUB</th>\n",
       "      <td>978113</td>\n",
       "      <td>-</td>\n",
       "      <td>977476</td>\n",
       "      <td>0,07 %</td>\n",
       "      <td>00637</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR/RUB</th>\n",
       "      <td>896770</td>\n",
       "      <td>-</td>\n",
       "      <td>889850</td>\n",
       "      <td>0,78 %</td>\n",
       "      <td>06920</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD/RUB</th>\n",
       "      <td>756995</td>\n",
       "      <td>-</td>\n",
       "      <td>756502</td>\n",
       "      <td>0,07 %</td>\n",
       "      <td>00493</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPY/RUB</th>\n",
       "      <td>07240</td>\n",
       "      <td>-</td>\n",
       "      <td>07235</td>\n",
       "      <td>0,06 %</td>\n",
       "      <td>00004</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CNY/RUB</th>\n",
       "      <td>111834</td>\n",
       "      <td>-</td>\n",
       "      <td>111760</td>\n",
       "      <td>0,07 %</td>\n",
       "      <td>00074</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHF/RUB</th>\n",
       "      <td>830603</td>\n",
       "      <td>-</td>\n",
       "      <td>829953</td>\n",
       "      <td>0,08 %</td>\n",
       "      <td>00650</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR/USD</th>\n",
       "      <td>11843</td>\n",
       "      <td>-</td>\n",
       "      <td>11840</td>\n",
       "      <td>0,03 %</td>\n",
       "      <td>00004</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR/GBP</th>\n",
       "      <td>09165</td>\n",
       "      <td>-</td>\n",
       "      <td>09130</td>\n",
       "      <td>0,39 %</td>\n",
       "      <td>00035</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR/JPY</th>\n",
       "      <td>1238250</td>\n",
       "      <td>-</td>\n",
       "      <td>1238000</td>\n",
       "      <td>0,02 %</td>\n",
       "      <td>00250</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR/CHF</th>\n",
       "      <td>10792</td>\n",
       "      <td>-</td>\n",
       "      <td>10793</td>\n",
       "      <td>-0,01 %</td>\n",
       "      <td>-00001</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD/EUR</th>\n",
       "      <td>08444</td>\n",
       "      <td>-</td>\n",
       "      <td>08446</td>\n",
       "      <td>-0,03 %</td>\n",
       "      <td>-00003</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD/GBP</th>\n",
       "      <td>07739</td>\n",
       "      <td>-</td>\n",
       "      <td>07739</td>\n",
       "      <td>0,00 %</td>\n",
       "      <td>00000</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD/JPY</th>\n",
       "      <td>1045650</td>\n",
       "      <td>-</td>\n",
       "      <td>1047500</td>\n",
       "      <td>-0,18 %</td>\n",
       "      <td>-01850</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD/CHF</th>\n",
       "      <td>09114</td>\n",
       "      <td>-</td>\n",
       "      <td>09115</td>\n",
       "      <td>-0,02 %</td>\n",
       "      <td>-00002</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPY/EUR</th>\n",
       "      <td>08100</td>\n",
       "      <td>-</td>\n",
       "      <td>08100</td>\n",
       "      <td>-0,03 %</td>\n",
       "      <td>00000</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPY/GBP</th>\n",
       "      <td>00074</td>\n",
       "      <td>-</td>\n",
       "      <td>00074</td>\n",
       "      <td>0,00 %</td>\n",
       "      <td>00000</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPY/USD</th>\n",
       "      <td>00096</td>\n",
       "      <td>-</td>\n",
       "      <td>00096</td>\n",
       "      <td>0,00 %</td>\n",
       "      <td>00000</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPY/CHF</th>\n",
       "      <td>08700</td>\n",
       "      <td>-</td>\n",
       "      <td>08700</td>\n",
       "      <td>0,54 %</td>\n",
       "      <td>00000</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP/USD</th>\n",
       "      <td>12921</td>\n",
       "      <td>-</td>\n",
       "      <td>12972</td>\n",
       "      <td>-0,39 %</td>\n",
       "      <td>-00051</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP/JPY</th>\n",
       "      <td>1351084</td>\n",
       "      <td>-</td>\n",
       "      <td>1351020</td>\n",
       "      <td>0,00 %</td>\n",
       "      <td>00065</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP/EUR</th>\n",
       "      <td>10913</td>\n",
       "      <td>-</td>\n",
       "      <td>10951</td>\n",
       "      <td>-0,35 %</td>\n",
       "      <td>-00038</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP/CHF</th>\n",
       "      <td>11772</td>\n",
       "      <td>-</td>\n",
       "      <td>11785</td>\n",
       "      <td>-0,11 %</td>\n",
       "      <td>-00013</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP/CHF</th>\n",
       "      <td>11772</td>\n",
       "      <td>-</td>\n",
       "      <td>11785</td>\n",
       "      <td>-0,11 %</td>\n",
       "      <td>-00013</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR/CHF</th>\n",
       "      <td>10792</td>\n",
       "      <td>-</td>\n",
       "      <td>10793</td>\n",
       "      <td>-0,01 %</td>\n",
       "      <td>-00001</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>JPY/CHF</th>\n",
       "      <td>08700</td>\n",
       "      <td>-</td>\n",
       "      <td>08700</td>\n",
       "      <td>0,54 %</td>\n",
       "      <td>00000</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD/CHF</th>\n",
       "      <td>09114</td>\n",
       "      <td>-</td>\n",
       "      <td>09115</td>\n",
       "      <td>-0,02 %</td>\n",
       "      <td>-00002</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EUR/UAH</th>\n",
       "      <td>333761</td>\n",
       "      <td>-</td>\n",
       "      <td>333578</td>\n",
       "      <td>0,05 %</td>\n",
       "      <td>00183</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>USD/UAH</th>\n",
       "      <td>281821</td>\n",
       "      <td>-</td>\n",
       "      <td>281750</td>\n",
       "      <td>0,03 %</td>\n",
       "      <td>00071</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CHF/UAH</th>\n",
       "      <td>309225</td>\n",
       "      <td>-</td>\n",
       "      <td>309106</td>\n",
       "      <td>0,04 %</td>\n",
       "      <td>00119</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GBP/UAH</th>\n",
       "      <td>364141</td>\n",
       "      <td>-</td>\n",
       "      <td>364049</td>\n",
       "      <td>0,03 %</td>\n",
       "      <td>00092</td>\n",
       "      <td>19.09.2020</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        Предложение Спрос Предыдущее закрытие        % Абсолютное значение  \\\n",
       "Валюта                                                                       \n",
       "GBP/RUB      978113     -              977476   0,07 %               00637   \n",
       "EUR/RUB      896770     -              889850   0,78 %               06920   \n",
       "USD/RUB      756995     -              756502   0,07 %               00493   \n",
       "JPY/RUB       07240     -               07235   0,06 %               00004   \n",
       "CNY/RUB      111834     -              111760   0,07 %               00074   \n",
       "CHF/RUB      830603     -              829953   0,08 %               00650   \n",
       "EUR/USD       11843     -               11840   0,03 %               00004   \n",
       "EUR/GBP       09165     -               09130   0,39 %               00035   \n",
       "EUR/JPY     1238250     -             1238000   0,02 %               00250   \n",
       "EUR/CHF       10792     -               10793  -0,01 %              -00001   \n",
       "USD/EUR       08444     -               08446  -0,03 %              -00003   \n",
       "USD/GBP       07739     -               07739   0,00 %               00000   \n",
       "USD/JPY     1045650     -             1047500  -0,18 %              -01850   \n",
       "USD/CHF       09114     -               09115  -0,02 %              -00002   \n",
       "JPY/EUR       08100     -               08100  -0,03 %               00000   \n",
       "JPY/GBP       00074     -               00074   0,00 %               00000   \n",
       "JPY/USD       00096     -               00096   0,00 %               00000   \n",
       "JPY/CHF       08700     -               08700   0,54 %               00000   \n",
       "GBP/USD       12921     -               12972  -0,39 %              -00051   \n",
       "GBP/JPY     1351084     -             1351020   0,00 %               00065   \n",
       "GBP/EUR       10913     -               10951  -0,35 %              -00038   \n",
       "GBP/CHF       11772     -               11785  -0,11 %              -00013   \n",
       "GBP/CHF       11772     -               11785  -0,11 %              -00013   \n",
       "EUR/CHF       10792     -               10793  -0,01 %              -00001   \n",
       "JPY/CHF       08700     -               08700   0,54 %               00000   \n",
       "USD/CHF       09114     -               09115  -0,02 %              -00002   \n",
       "EUR/UAH      333761     -              333578   0,05 %               00183   \n",
       "USD/UAH      281821     -              281750   0,03 %               00071   \n",
       "CHF/UAH      309225     -              309106   0,04 %               00119   \n",
       "GBP/UAH      364141     -              364049   0,03 %               00092   \n",
       "\n",
       "              Время  \n",
       "Валюта               \n",
       "GBP/RUB  19.09.2020  \n",
       "EUR/RUB  19.09.2020  \n",
       "USD/RUB  19.09.2020  \n",
       "JPY/RUB  19.09.2020  \n",
       "CNY/RUB  19.09.2020  \n",
       "CHF/RUB  19.09.2020  \n",
       "EUR/USD  19.09.2020  \n",
       "EUR/GBP  19.09.2020  \n",
       "EUR/JPY  19.09.2020  \n",
       "EUR/CHF  19.09.2020  \n",
       "USD/EUR  19.09.2020  \n",
       "USD/GBP  19.09.2020  \n",
       "USD/JPY  19.09.2020  \n",
       "USD/CHF  19.09.2020  \n",
       "JPY/EUR  19.09.2020  \n",
       "JPY/GBP  19.09.2020  \n",
       "JPY/USD  19.09.2020  \n",
       "JPY/CHF  19.09.2020  \n",
       "GBP/USD  19.09.2020  \n",
       "GBP/JPY  19.09.2020  \n",
       "GBP/EUR  19.09.2020  \n",
       "GBP/CHF  19.09.2020  \n",
       "GBP/CHF  19.09.2020  \n",
       "EUR/CHF  19.09.2020  \n",
       "JPY/CHF  19.09.2020  \n",
       "USD/CHF  19.09.2020  \n",
       "EUR/UAH  19.09.2020  \n",
       "USD/UAH  19.09.2020  \n",
       "CHF/UAH  19.09.2020  \n",
       "GBP/UAH  19.09.2020  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "display(html)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
